'''-- 技术经理人认证表
CREATE TABLE IF NOT EXISTS wx_manager_authentication (

    wx_id INT PRIMARY KEY, -- 微信号
    real_name VARCHAR(50), -- 真实姓名
    phone_number VARCHAR(50), -- 手机号
    sex VARCHAR(10), -- 性别
    birth_date VARCHAR(50), -- 出生日期
    unit_name VARCHAR(50), -- 单位名称    
    unit_property VARCHAR(50), -- 单位性质
    unit_address VARCHAR(2000), -- 常驻地址
    academic_background VARCHAR(50), -- 教育背景
    -- 职务
    job VARCHAR(50),
    -- 职称
    job_title VARCHAR(50),
    -- 是否持有技术经理人资格证书
    certificate BOOLEAN DEFAULT false,
    -- 证书类型 高 中 低 级
    certificate_level VARCHAR(50),
    -- 获得荣誉
    awards VARCHAR(2000),
    -- 社会兼职
    social_job VARCHAR(200),
    -- 个人简介
    personal_introduction VARCHAR(2000),
    -- 个人照片 一个文件地址
    personal_photo VARCHAR(100),
    -- 个人履历 一个文件地址
    personal_resume VARCHAR(100),
    -- 证件扫描件 一个文件地址
    certificate_photo VARCHAR(100),

    -- 认证状态
    authentication_status BOOLEAN DEFAULT false,
    -- 认证通过时间
    authentication_time TIMESTAMP,

    -- 信息提交时间
    submit_time TIMESTAMP DEFAULT now(),

    FOREIGN KEY (wx_id) REFERENCES wx_user(wx_id)

);'''

import datetime
import logging
import psycopg2


logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
class WxManagerAuthenticationMethods: 

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn


    def get_manager_authentication_by_wx_id(self, wx_id):
        
        sql = "SELECT * FROM wx_manager_authentication WHERE wx_id = %s"
        self.cursor.execute(sql, (wx_id,))
        result = self.cursor.fetchone()
        return result

    # 弃用
    def _insert_or_update_manager_authentication(self, wx_id, real_name, phone_number, sex, birth_date, unit_name, unit_property, unit_address, academic_background, job, job_title, certificate, certificate_level, awards, social_job, personal_introduction, personal_photo, personal_resume, certificate_photo):

        if self.get_manager_authentication_by_wx_id(wx_id): # 如果存在则更新
            sql = "UPDATE wx_manager_authentication SET real_name = %s, phone_number = %s, sex = %s, birth_date = %s, unit_name = %s, unit_property = %s, unit_address = %s, academic_background = %s, job = %s, job_title = %s, certificate = %s, certificate_level = %s, awards = %s, social_job = %s, personal_introduction = %s, personal_photo = %s, personal_resume = %s, certificate_photo = %s, authentication_time = %s WHERE wx_id = %s"

            self.cursor.execute(sql, (real_name, phone_number, sex, birth_date, unit_name, unit_property, unit_address, academic_background, job, job_title, certificate, certificate_level, awards, social_job, personal_introduction, personal_photo, personal_resume, certificate_photo, datetime.datetime.now(), wx_id))
            self.conn.commit()
            logger.info("更新技术经理人认证信息成功")
        else: # 不存在则插入
            sql = "INSERT INTO wx_manager_authentication (wx_id, real_name, phone_number, sex, birth_date, unit_name, unit_property, unit_address, academic_background, job, job_title, certificate, certificate_level, awards, social_job, personal_introduction, personal_photo, personal_resume, certificate_photo, authentication_time) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            self.cursor.execute(sql, (wx_id, real_name, phone_number, sex, birth_date, unit_name, unit_property, unit_address, academic_background, job, job_title, certificate, certificate_level, awards, social_job, personal_introduction, personal_photo, personal_resume, certificate_photo, datetime.datetime.now()))
            self.conn.commit()
            logger.info("插入技术经理人认证信息成功")


    def insert_or_update_manager_authentication(self, wx_id, awards, job_title, social_job, certificate_photo, certificate_level):

        if self.get_manager_authentication_by_wx_id(wx_id): # 如果存在则更新
            sql = "UPDATE wx_manager_authentication SET awards = %s, job_title = %s, social_job = %s, certificate_photo = %s, certificate_level = %s WHERE wx_id = %s"
            self.cursor.execute(sql, (awards, job_title, social_job, certificate_photo, certificate_level, wx_id))
            self.conn.commit()
            logger.info("更新技术经理人认证信息成功")
        else: # 不存在则插入
            sql = "INSERT INTO wx_manager_authentication (wx_id, awards, job_title, social_job, certificate_photo, certificate_level) VALUES (%s, %s, %s, %s, %s, %s)"
            self.cursor.execute(sql, (wx_id, awards, job_title, social_job, certificate_photo, certificate_level))
            self.conn.commit()
            logger.info("插入技术经理人认证信息成功")

    def update_authentication_status(self, wx_id, authentication_status):
        sql = "UPDATE wx_manager_authentication SET authentication_status = %s, authentication_time = %s WHERE wx_id = %s"
        self.cursor.execute(sql, (authentication_status, datetime.datetime.now(), wx_id))
        self.conn.commit()
        logger.info("更新技术经理人认证状态成功")


    def get_all_manager_authentication(self, page, limit):
        start = (page - 1) * limit
        end = start + limit
        sql = "SELECT * FROM wx_manager_authentication ORDER BY submit_time DESC LIMIT %s OFFSET %s"  
        self.cursor.execute(sql, (limit, start))
        result = self.cursor.fetchall()
        return result

    def get_all_manager_authentication_count(self):
        sql = "SELECT COUNT(*) FROM wx_manager_authentication"
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        return result["count"]
    
    # 获取认证通过的技术经理人
    def get_authentication_status_true(self, page, limit):
        sql = "SELECT * FROM wx_manager_authentication WHERE authentication_status = true ORDER BY submit_time DESC LIMIT %s OFFSET %s"
        self.cursor.execute(sql, (limit, (page - 1) * limit))
        result = self.cursor.fetchall()
        return result
    # 获取认证通过的技术经理人数量
    def get_authentication_status_true_count(self):
        sql = "SELECT COUNT(*) FROM wx_manager_authentication WHERE authentication_status = true"
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        return result["count"]
    
    # 获取未认证的技术经理人
    def get_authentication_status_false(self, page, limit):
        sql = "SELECT * FROM wx_manager_authentication WHERE authentication_status = false ORDER BY submit_time DESC LIMIT %s OFFSET %s"
        self.cursor.execute(sql, (limit, (page - 1) * limit))
        result = self.cursor.fetchall()
        return result
    # 获取未认证的技术经理人数量
    def get_authentication_status_false_count(self):
        sql = "SELECT COUNT(*) FROM wx_manager_authentication WHERE authentication_status = false"
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        return result["count"]
    